home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Power Programmierung
/
Power-Programmierung (Tewi)(1994).iso
/
magazine
/
dbms_mag
/
9107
/
sql_sibs
< prev
next >
Wrap
Text File
|
1991-06-14
|
9KB
|
297 lines
SQL SIBLINGS, DBMS, JULY 1991
*****************************Listings***************************
Listing 1
SQL> SELECT Pat.PatNo, Pat.PatName, COUNT(*) "BACK OPER"
2 FROM Pat, Proc
3 WHERE Pat.PatNo = Proc.PatNo
4 AND Proc.Proc = 'BACK'
5 GROUP BY Pat.PatNo, Pat.PatName
6 HAVING COUNT(*) > 1;
PAT PATNAME BACK OPER
--- ------------ ----------
001 John Brown 2
Listing 2.
SQL> SELECT Pat.PatNo, Pat.PatName, MIN(Rx.RxDate) FIRST,
MAX(Rx.RxDate) LAST
2 FROM Pat, Rx
3 WHERE Pat.PatNo = Rx.PatNo
4 AND Rx.MedName = 'PROPOX'
5 GROUP BY Pat.PatNo, Pat.PatName
6 HAVING '01-JAN-89' <= MIN(Rx.RxDate)
7 AND MAX(Rx.RxDate) <= '31-DEC-89';
PAT PATNAME FIRST LAST
--- ------------ --------- ---------
003 Jim Green 30-JAN-89 06-DEC-89
Listing 3.
SQL> SELECT Pat.PatNo, Pat.PatName, Proc.ProcDate
2 FROM Pat, Proc
3 WHERE Pat.PatNo = Proc.PatNo
4 AND Proc.Proc = 'BACK';
PAT PATNAME PROCDATE
--- ------------ ---------
001 John Brown 06-JUL-89
001 John Brown 30-AUG-90
002 Mary White 23-SEP-90
SQL> SELECT Pat.PatNo, Pat.PatName, Rx.RxDate
2 FROM Pat, Rx
3 WHERE Pat.PatNo = Rx.PatNo
4 AND Rx.MedName = 'PROPOX';
PAT PATNAME RXDATE
--- ------------ ---------
001 John Brown 12-FEB-87
001 John Brown 02-MAR-89
003 Jim Green 30-JAN-89
003 Jim Green 06-DEC-89
Listing 4.
SQL> SELECT Pat.PatNo, Pat.PatName, Proc.ProcDate, Rx.RxDate
2 FROM Pat, Proc, Rx
3 WHERE Pat.PatNo = Proc.PatNo
4 AND Proc.Proc = 'BACK'
5 AND Pat.PatNo = Rx.PatNo
6 AND Rx.MedName = 'PROPOX';
PAT PATNAME PROCDATE RXDATE
--- ------------ --------- ---------
001 John Brown 06-JUL-89 12-FEB-87
001 John Brown 30-AUG-90 12-FEB-87
001 John Brown 06-JUL-89 02-MAR-89
001 John Brown 30-AUG-90 02-MAR-89
Listing 5.
SQL> SELECT Pat.PatNo, Proc.PatNo, Proc.Proc, Proc.ProcDate,
2 Rx.PatNo, Rx.MedName, Rx.RxDate
3 FROM Pat, Proc, Rx
4 WHERE Pat.PatNo = Proc.PatNo
5 AND Pat.PatNo = Rx.PatNo;
PAT PAT PROC PROCDATE PAT MEDNAME RXDATE
--- --- ------ --------- --- -------- ---------
001 001 BACK 06-JUL-89 001 PROPOX 12-FEB-87
001 001 KNEE 14-DEC-89 001 PROPOX 12-FEB-87
001 001 BACK 30-AUG-90 001 PROPOX 12-FEB-87
001 001 BACK 06-JUL-89 001 PROPOX 02-MAR-89
001 001 KNEE 14-DEC-89 001 PROPOX 02-MAR-89
001 001 BACK 30-AUG-90 001 PROPOX 02-MAR-89
002 002 BACK 23-SEP-90 002 ACETA 09-APR-90
002 002 BACK 23-SEP-90 002 ACETA 12-JAN-91
Listing 6.
SQL> SELECT Pat.PatNo, Pat.PatName
2 FROM Pat, Proc
3 WHERE Pat.PatNo = Proc.PatNo
4 AND Proc.Proc = 'BACK'
5 GROUP BY Pat.PatNo, Pat.PatName
6 INTERSECT
7 SELECT Pat.PatNo, Pat.PatName
8 FROM Pat, Rx
9 WHERE Pat.PatNo = Rx.PatNo
10 AND Rx.MedName = 'PROPOX'
11 GROUP BY Pat.PatNo, Pat.PatName;
PAT PATNAME
--- ------------
001 John Brown
Listing 7
SQL> SELECT Pat.PatNo, Pat.PatName, 'Back operation(s)' Encounter
2 FROM Pat, Proc
3 WHERE Pat.PatNo = Proc.PatNo
4 AND Proc.Proc = 'BACK'
5 GROUP BY Pat.PatNo, Pat.PatName
6 UNION
7 SELECT Pat.PatNo, Pat.PatName, 'Propox prescription(s)'
8 FROM Pat, Rx
9 WHERE Pat.PatNo = Rx.PatNo
10 AND Rx.MedName = 'PROPOX'
11 GROUP BY Pat.PatNo, Pat.PatName;
PAT PATNAME ENCOUNTER
--- ------------ ----------------------
001 John Brown Back operation(s)
001 John Brown Propox prescription(s)
002 Mary White Back operation(s)
003 Jim Green Propox prescription(s)
Listing 8
SQL> SELECT Pat.PatNo, Pat.PatName, 'Back operation(s)' Encounter,
2 SUM(Proc.Cost) Cost
3 FROM Pat, Proc
4 WHERE Pat.PatNo = Proc.PatNo
5 AND Proc.Proc = 'BACK'
6 GROUP BY Pat.PatNo, Pat.PatName
7 UNION
8 SELECT Pat.PatNo, Pat.PatName, 'Propox prescription(s)',
9 SUM(Rx.Cost)
10 FROM Pat, Rx
11 WHERE Pat.PatNo = Rx.PatNo
12 AND Rx.MedName = 'PROPOX'
13 GROUP BY Pat.PatNo, Pat.PatName;
PAT PATNAME ENCOUNTER COST
--- ------------ ---------------------- ------------
001 John Brown Back operation(s) $9,200.00
001 John Brown Propox prescription(s) $105.00
002 Mary White Back operation(s) $5,200.00
003 Jim Green Propox prescription(s) $95.00
Listing 9.
SQL> CREATE VIEW ProcSum (PatNo, PatName, Cost)
2 AS
3 SELECT Pat.PatNo, Pat.PatName, SUM(Proc.Cost)
4 FROM Pat, Proc
5 WHERE Pat.PatNo = Proc.PatNo
6 AND Proc.Proc = 'BACK'
7 GROUP BY Pat.PatNo, Pat.PatName;
iew created.
SQL> SELECT S.PatNo, S.PatName, S.Cost "PROC COST",
SUM(Rx.Cost) "RX COST"
2 FROM ProcSum S, Rx
3 WHERE S.PatNo = Rx.PatNo
4 AND Rx.MedName = 'PROPOX'
5 GROUP BY S.PatNo, S.PatName, S.Cost;
PAT PATNAME PROC COST RX COST
--- ------------ ------------ ------------
001 John Brown $9,200.00 $105.00
Listing 10
SQL> CREATE VIEW ProcSum (PatNo, PatName, Proc, Cost)
2 AS
3 SELECT Pat.PatNo, Pat.PatName, MAX(Proc.Proc),
SUM(Proc.Cost)
4 FROM Pat, Proc
5 WHERE Pat.PatNo = Proc.PatNo (+)
6 AND Proc.Proc (+) = 'BACK'
7 GROUP BY Pat.PatNo, Pat.PatName;
View created.
SQL> SELECT S.PatNo, S.PatName, S.Cost "PROC COST",
SUM(Rx.Cost) "RX COST"
2 FROM ProcSum S, Rx
3 WHERE S.PatNo = Rx.PatNo (+)
4 AND (S.Proc IS NOT NULL OR Rx.MedName = 'PROPOX')
5 GROUP BY S.PatNo, S.PatName, S.Cost;
PAT PATNAME PROC COST RX COST
--- ------------ ------------ ------------
001 John Brown $9,200.00 $105.00
002 Mary White $5,200.00 $70.00
003 Jim Green $95.00
Listing 11
SQL> CREATE VIEW ProcSum (PatNo, PatName, FirstProc,
LastProc)
2 AS
3 SELECT Pat.PatNo, Pat.PatName, MIN(Proc.ProcDate),
MAX(Proc.ProcDate)
4 FROM Pat, Proc
5 WHERE Pat.PatNo = Proc.PatNo
6 AND Proc.Proc = 'BACK'
7 GROUP BY Pat.PatNo, Pat.PatName;
View created.
SQL> SELECT S.PatNo, S.PatName, S.FirstProc, S.LastProc,
2 MIN(Rx.RxDate) "FIRST RX", MAX(Rx.RxDate) "LAST RX"
3 FROM ProcSum S, Rx
4 WHERE S.PatNo = Rx.PatNo
5 HAVING MIN(Rx.RxDate) <= S.FirstProc
6 AND S.LastProc <= MAX(Rx.RxDate)
7 GROUP BY S.PatNo, S.PatName, S.FirstProc, S.LastProc;
PAT PATNAME FIRSTPROC LASTPROC FIRST RX LAST RX
--- ------------ --------- --------- --------- ---------
002 Mary White 23-SEP-90 23-SEP-90 09-APR-90 12-JAN-91
Listing 12
SQL> SELECT Pat.PatNo, Pat.PatName,
2 'Back operation' Encounter, Proc.ProcDate EnctrDate
3 FROM Pat, Proc
4 WHERE Pat.PatNo = Proc.PatNo
5 AND Proc.Proc = 'BACK'
6 UNION
7 SELECT Pat.PatNo, Pat.PatName,
8 'Propox prescription', Rx.RxDate
9 FROM Pat, Rx
10 WHERE Pat.PatNo = Rx.PatNo
11 AND Rx.MedName = 'PROPOX';
PAT PATNAME ENCOUNTER ENCTRDATE
--- ------------ ------------------- ---------
001 John Brown Back operation 06-JUL-89
001 John Brown Back operation 30-AUG-90
001 John Brown Propox prescription 12-FEB-87
001 John Brown Propox prescription 02-MAR-89
002 Mary White Back operation 23-SEP-90
003 Jim Green Propox prescription 30-JAN-89
003 Jim Green Propox prescription 06-DEC-89
Listing 13
SQL> SELECT Pat.PatNo, Pat.PatName,
2 'Back operation' Encounter, Proc.ProcDate EnctrDate
3 FROM Pat, Proc
4 WHERE Pat.PatNo = Proc.PatNo
5 AND Proc.Proc = 'BACK'
6 AND Pat.PatNo IN (SELECT DISTINCT Rx.PatNo
7 FROM Rx
8 WHERE Rx.MedName = 'PROPOX')
9 UNION
10 SELECT Pat.PatNo, Pat.PatName,
11 'Propox prescription', Rx.RxDate
12 FROM Pat, Rx
13 WHERE Pat.PatNo = Rx.PatNo
14 AND Rx.MedName = 'PROPOX'
15 AND Pat.PatNo IN (SELECT DISTINCT Proc.PatNo
16 FROM Proc
17 WHERE Proc.Proc = 'BACK');
PAT PATNAME ENCOUNTER ENCTRDATE
--- ------------ ------------------- ---------
001 John Brown Back operation 06-JUL-89
001 John Brown Back operation 30-AUG-90
001 John Brown Propox prescription 12-FEB-87
001 John Brown Propox prescription 02-MAR-89